﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Generic;
using Chi.Lib.DBHelper;

public partial class ShowTableSchema : System.Web.UI.Page
{
    private FuncItem Item
    {
        get
        {
            return (FuncItem)Enum.Parse(typeof(FuncItem), ux_ItemSel.SelectedItem.Text);
        }
    }
    private String Msg
    {
        set
        {
            ux_Msg.Text = value;
        }
        get
        {
            return ux_Msg.Text;
        }
    }
    private String ItemNM
    {
        set {
            ux_ItemNM.Text = value;
        }
    }
    private string strSql = "";
    private SqlConnection myConnection = new SqlConnection(DBHelperConnection._ConnStr);
    private SqlCommand myCommand;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            getTableName(ux_ItemSel.SelectedValue);
            getProcName();
        }
    }

    /// <summary>
    /// 下拉選單
    /// </summary>
    /// <param name="strType"></param>
    private void getTableName(string strType)
    {
        strSql = string.Format("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE (TABLE_TYPE = '{0}') and(TABLE_NAME!='dtproperties') order by TABLE_NAME", ux_ItemSel.SelectedValue);

        myCommand = new SqlCommand(strSql, myConnection);

        myConnection.Open();

        using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (dr.Read())
            {
                ux_ListItem.Items.Add(dr.GetSqlString(2).Value);
            }
        }
        myConnection.Close();
    }

    /// <summary>
    /// 下拉選單
    /// </summary>
    private void getProcName()
    {
        strSql = " select * from sys.procedures Where substring( [name],1,3) not in('sp_') ";
        myCommand = new SqlCommand(strSql, myConnection);
        myConnection.Open();

        using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
        {
            while (dr.Read())
            {
                ux_ListItem.Items.Add(dr.GetSqlString(0).Value);
            }
        }
        myConnection.Close();
    }

    /// <summary>
    /// 挑Table or View
    /// </summary>
    protected void ux_ItemSel_SelectedIndexChanged(object sender, EventArgs e)
    {
        ux_ListItem.Items.Clear();
        fnButtonVisable(ux_ItemSel.SelectedValue.Equals("SP"));
    }

    protected void ux_Schema_Click(object sender, EventArgs e)
    {
        ItemNM = ux_ListItem.SelectedItem.Value;
        strSql = string.Format("select P.*,Q.value AS Descriptions FROM (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='{0}' ) as P LEFT OUTER JOIN (SELECT * FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', '{0}', 'column', default) ) as Q ON P.COLUMN_NAME = Q.objname collate Chinese_Taiwan_Stroke_CI_AS", ux_ListItem.SelectedItem);
        myCommand = new SqlCommand(strSql, myConnection);
        myConnection.Open();

        #region SqlDataReader...放棄使用

        //using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
        //{
        //    gvView.DataSource = dr;
        //    gvView.DataBind();
        //}

        #endregion

        #region 撈主要資料

        SqlDataAdapter da = new SqlDataAdapter(myCommand);
        DataSet ds = new DataSet();
        da.Fill(ds, "main");

        #endregion

        #region 撈主鍵
        //系統檢視表 或 系統預存程序 都可以執行

        //系統檢視表
        //strSql = string.Format("select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE Where TABLE_NAME = '{0}'", ddlTableName.SelectedItem);
        myCommand = new SqlCommand(strSql, myConnection);
        //系統預存程序
        SqlCommand comm = new SqlCommand("sys.sp_pkeys", myConnection);
        comm.CommandType = CommandType.StoredProcedure;
        comm.Parameters.AddWithValue("table_name", ux_ListItem.SelectedItem.Text);

        SqlDataAdapter daPK = new SqlDataAdapter(comm);
        DataSet dspk = new DataSet();

        daPK.Fill(dspk, "pk");
        #endregion

        #region 整理資料
        List<object> pklist = new List<object>();
        foreach (DataRow dr in dspk.Tables["pk"].Rows)
        {
            pklist.Add(dr["COLUMN_NAME"]);
        }

        ds.Tables["main"].Columns.Add("pk");

        foreach (DataRow dr in ds.Tables["main"].Rows)
        {
            try
            {
                if (pklist.Remove(dr["COLUMN_NAME"]))
                    dr["pk"] = "*";
            }
            catch { }
        }
        #endregion

        #region Bind Data
        this.gvView.DataSource = ds.Tables["main"];
        this.gvView.DataBind();
        #endregion

        myConnection.Close();
    }

    protected void fnButtonVisable(bool IsSP)
    {
        this.ux_Schema.Visible = !IsSP;
        this.ux_Script.Visible = !IsSP;
        this.LinkButton3.Visible = IsSP;
        if (IsSP == true)
            getProcName();
        else
            getTableName(ux_ItemSel.SelectedValue);
    }

    public void ListSP()
    {
        ServerConnection conn = new ServerConnection();
        conn.ConnectionString = DBHelperConnection._ConnStr;
        Server server = new Server(conn);

        Database db = server.Databases["Northwind"];

        StoredProcedureCollection spc = db.StoredProcedures;

        ux_ListItem.DataSource = spc;
        ux_ListItem.DataBind();
    }

    protected void ux_Script_Click(object sender, EventArgs e)
    {
        //string openurl = string.Format("<script>window.open('ShowScript.aspx?selected={0}','','menubar=no,status=yes,scrollbars=no,top=100,left=400,toolbar=no,width=600,height=500');</script>", ux_ListItem.SelectedItem.Text);
        //Response.Write(openurl);
    }
    
    protected void LinkButton3_Click(object sender, EventArgs e)
    {
        //string openurl = string.Format("<script>window.open('ShowScript.aspx?selected={0}','','menubar=no,status=yes,scrollbars=no,top=100,left=400,toolbar=no,width=600,height=500');</script>", ux_ListItem.SelectedItem.Text);
        //Response.Write(openurl);
    }
}
